import java.io.File;
import java.sql.SQLException;
import java.sql.Timestamp;
import java.util.Calendar;
import java.util.Date;
import java.util.HashMap;
import java.util.LinkedList;
import java.util.SimpleTimeZone;
import java.io.FileReader;

import org.apache.commons.lang.StringEscapeUtils;
import org.xml.sax.SAXException;
import org.xml.sax.XMLReader;
import org.xml.sax.Attributes;
import org.xml.sax.InputSource;
import org.xml.sax.helpers.XMLReaderFactory;
import org.xml.sax.helpers.DefaultHandler;

import java.sql.*;
import java.text.DateFormat;
import java.text.SimpleDateFormat;

import javax.sql.*;

class Comment
{
	private String bugid = null;
	private String author = null;
	private String when = null;
	private String what = null;
	
	//method to set element given the element id
	public void setElement(int id, String content)
	{
		switch (id)
		{
			case BugReader.AUTHOR:
			{
				author = content;
				
				break;
			}
			case BugReader.WHEN:
			{
				when = content;
				
				break;
			}
			
			case BugReader.BUGID:
			{
				bugid = content;
				
				break;
			}
			case BugReader.WHAT:
			{
				what = content;
				
				break;
			}
			
			default:
			{
				break;
			}
		}
	}
	
	//method to get variable given the variable id
	public String getElement(int viarableId)
	{
		switch (viarableId)
		{
			case BugReader.AUTHOR:
			{
				return author;
			}
			case BugReader.WHEN:
			{
				return when;
			}
			case BugReader.WHAT:
			{
				return what;
			}
			case BugReader.BUGID:
			{
				return bugid;
			}
			default:
			{
				throw new Error("illegal variableId");
			}
		}
	}
}

class CommentList
{	
	PreparedStatement cmps = null;
	
	private int count = 0;
	
	public CommentList(PreparedStatement cmps)
	{	
		this.cmps = cmps;
		
		count = 0;
	}
	
	public void add(Comment cm) throws NumberFormatException, SQLException
	{	
		/*
		if (cm.getElement(BugReader.BUGID).equals("2885") 
				&& cm.getElement(BugReader.AUTHOR).equals("jbq+legacy@google.com"))
		{
			
			System.err.println("-->" + cm.getElement(BugReader.WHAT) + "<--");
			System.err.println(cm.getElement(BugReader.WHAT).equals(""));
		}
		*/
		
		count++;
		
		String bugid = cm.getElement(BugReader.BUGID);
		if (bugid == null)
		{
			cmps.setNull(1, Types.INTEGER);
		}
		else
		{
			cmps.setInt(1, Integer.parseInt(bugid));
		}
		
		String author = cm.getElement(BugReader.AUTHOR);
		if (author == null)
		{
			cmps.setNull(2, Types.VARCHAR);
		}
		else
		{
			cmps.setString(2, author);
		}
		
		String when = cm.getElement(BugReader.WHEN);
		if (when == null)
		{
			cmps.setNull(3, Types.CHAR);
		}
		else
		{
			cmps.setTimestamp(3, BugReader.toTimestamp(when));
		}
		
		String what = cm.getElement(BugReader.WHAT);
		if (what == null)
		{
			cmps.setNull(4, Types.CLOB);
		}
		else
		{
			cmps.setString(4, what);
		}
		
		cmps.addBatch();
		
		if (count > BugReader.COMMENT_BATCH_SIZE)
		{
			count = 0;
			
			outsource();
		}
	}
	
	public void outsource() throws SQLException
	{
		cmps.executeBatch();
	}
	
}


class BugBody
{
	private String bugid = null;
	private String title = null;
	private String status = null;
	private String owner = null;
	private String closedOn = null;
	private String type = null;
	private String priority = null;
	private String component = null;
	private String stars = null;
	private String reportedBy = null;
	private String openedDate = null;
	
	//method to set variable given the variable id
	public void setElement(int id, String content)
	{
		switch (id)
		{
			case BugReader.BUGID:
			{
				bugid = content;
				
				break;
			}
			case BugReader.TITLE:
			{
				title = content;
				
				break;
			}
			case BugReader.STATUS:
			{
				status = content;
				
				break;
			}
			case BugReader.OWNER:
			{
				owner = content;
				
				break;
			}
			case BugReader.CLOSEDON:
			{
				closedOn = content;
				
				break;
			}
			case BugReader.TYPE:
			{
				type = content;
				
				break;
			}
			case BugReader.PRIORITY:
			{
				priority = content;
				
				break;
			}
			case BugReader.COMPONENT:
			{
				component = content;
				
				break;
			}
			case BugReader.STARS:
			{
				stars = content;
				
				break;
			}
			case BugReader.REPORTEDBY:
			{
				reportedBy = content;
				
				break;
			}
			case BugReader.OPENEDDATE:
			{
				this.openedDate = content;
				
				break;
			}
			default:
			{
				break;
			}
		}
	}
	
	//method to get variable given the variable id
	public String getElement(int id)
	{
		switch (id)
		{
			case BugReader.BUGID:
			{
				return bugid;
			}
			case BugReader.TITLE:
			{
				return title;
			}
			case BugReader.STATUS:
			{
				return status;
			}
			case BugReader.OWNER:
			{
				return owner;
			}
			case BugReader.CLOSEDON:
			{
				return closedOn;
			}
			case BugReader.TYPE:
			{
				return type;
			}
			case BugReader.PRIORITY:
			{
				return priority;
			}
			case BugReader.COMPONENT:
			{
				return component;
			}
			case BugReader.STARS:
			{
				return stars;
			}
			case BugReader.REPORTEDBY:
			{
				return reportedBy;
			}
			case BugReader.OPENEDDATE:
			{
				return openedDate;
			}
			default:
			{
				break;
			}
		}
		
		return null;
	}
}

class BugBodyList
{	
	PreparedStatement bbps = null;
	
	private int count = 0;
	
	public BugBodyList(PreparedStatement bbps)
	{		
		this.bbps = bbps;
		
		count = 0;
	}
	
	public void add(BugBody bb) throws NumberFormatException, SQLException
	{	
		count++;
		
		//System.out.println("====================");
		
		//System.out.println("bugid --> " + bb.getElement(bb.BUGID));
		
		String bugid = bb.getElement(BugReader.BUGID);
		
		/*
		if (bugid.equals("19727"))
		{
			System.err.println("----------------");
			
		}
		*/
		
		if (bugid == null)
		{
			bbps.setNull(1, Types.INTEGER);
		}
		else
		{
			bbps.setInt(1, Integer.parseInt(bugid));
		}
		
		String title = bb.getElement(BugReader.TITLE);
		if (title == null)
		{
			bbps.setNull(2, Types.VARCHAR);
		}
		else
		{
			bbps.setString(2, title);
		}
		
		String status = bb.getElement(BugReader.STATUS);
		if (status == null)
		{
			bbps.setNull(3, Types.VARCHAR);
		}
		else
		{
			bbps.setString(3, status);
		}
		
		String owner = bb.getElement(BugReader.OWNER);
		if (owner == null)
		{
			bbps.setNull(4, Types.VARCHAR);
		}
		else
		{
			bbps.setString(4, owner);
		}
		
		String closedOn = bb.getElement(BugReader.CLOSEDON);
		if (closedOn == null)
		{	
			bbps.setNull(5, Types.CHAR);
		}
		else
		{
			bbps.setTimestamp(5, BugReader.toTimestamp(closedOn));
		}
		
		String type = bb.getElement(BugReader.TYPE);
		if (type == null)
		{
			bbps.setNull(6, Types.VARCHAR);
		}
		else
		{
			bbps.setString(6, type);
		}
		
		String priority = bb.getElement(BugReader.PRIORITY);
		if (priority == null)
		{
			bbps.setNull(7, Types.VARCHAR);
		}
		else
		{
			bbps.setString(7, priority);
		}
		
		String component = bb.getElement(BugReader.COMPONENT);
		if (component == null)
		{
			bbps.setNull(8, Types.VARCHAR);
		}
		else
		{
			bbps.setString(8, component);
		}
		
		String stars = bb.getElement(BugReader.STARS);
		if (stars == null)
		{
			bbps.setNull(9, Types.INTEGER);
		}
		else
		{
			bbps.setInt(9, Integer.parseInt(stars));
		}
		
		String reportedBy = bb.getElement(BugReader.REPORTEDBY);
		if (reportedBy == null)
		{
			bbps.setNull(10, Types.VARCHAR);
		}
		else
		{
			bbps.setString(10, reportedBy);
		}
		
		String openedDate = bb.getElement(BugReader.OPENEDDATE);
		if (openedDate == null)
		{
			bbps.setNull(11, Types.CHAR);
		}
		else
		{
			bbps.setTimestamp(11, BugReader.toTimestamp(openedDate));
		}
		
		
		bbps.addBatch();
		
		if (count > BugReader.BUGBODY_BATCH_SIZE)
		{
			count = 0;
			
			outsource();
		}
	}
	
	public void outsource() throws SQLException
	{
		bbps.executeBatch();
	}
}

class Description
{
	private String bugid = null;
	private String description = null;
	
	//method to set element
	public void setElement(int id, String content)
	{
		switch (id)
		{
			case BugReader.BUGID:
			{
				bugid = content;
				
				break;
			}
			case BugReader.DESCRIPTION:
			{
				description = content;
				
				break;
			}
			default:
			{
				break;
			}
		}
	}
	
	//method to get element
	public String getElement(int id)
	{
		switch (id)
		{
			case BugReader.BUGID:
			{
				return bugid;
			}
			case BugReader.DESCRIPTION:
			{
				return description;
			}
			default:
			{
				break;
			}
		}
		
		return null;
	}	
}

class DescriptionList
{	
	PreparedStatement dsps = null;
	
	private int count = 0;
	
	public DescriptionList(PreparedStatement dsps)
	{		
		this.dsps = dsps;
		
		count = 0;
	}
	
	public void add(Description ds) throws NumberFormatException, SQLException
	{	
		count++;
		
		//System.out.println("====================");
		
		//System.out.println("bugid --> " + bc.getBugId());
		
		String bugid = ds.getElement(BugReader.BUGID);
		if (bugid == null)
		{
			dsps.setNull(1, Types.INTEGER);
		}
		else
		{
			dsps.setInt(1, Integer.parseInt(bugid));
		}
		
		String description = ds.getElement(BugReader.DESCRIPTION);
		if (description == null)
		{
			dsps.setNull(2, Types.CLOB);
		}
		else
		{
			dsps.setString(2, description);
		}
		
		dsps.addBatch();
		
		if (count > BugReader.DESCRIPTION_BATCH_SIZE)
		{
			count = 0;
			
			outsource();
		}
	}
	
	public void outsource() throws SQLException
	{
		dsps.executeBatch();
	}
}

public class BugReader
{
	final public static int BUG = 0;
	
	final public static int BUGID = 1;
	final public static int TITLE = 2;
	final public static int STATUS = 3;
	final public static int OWNER = 4;
	final public static int CLOSEDON = 5;
	final public static int TYPE = 6;
	final public static int PRIORITY = 7;
	final public static int COMPONENT = 8;
	final public static int STARS = 9;
	final public static int REPORTEDBY = 10;
	final public static int OPENEDDATE = 11;
	
	final public static int DESCRIPTION = 12;

	final public static int AUTHOR = 13;
	final public static int WHEN = 14;
	final public static int WHAT = 15;
	
	final public static int COMMENT = 16;
	
	//the source XML file
	private String filename = null;
	
	//xml reader, used to read in the 
	private XMLReader xr = null;
	
	//database object
	static DB db = null;
	
	//batch size
	final public static int COMMENT_BATCH_SIZE = 10;
	final public static int DESCRIPTION_BATCH_SIZE = 10;
	final public static int BUGBODY_BATCH_SIZE = 100;
	
	//prepared statement
	private PreparedStatement bbps = null;
	private PreparedStatement cmps = null;
	private PreparedStatement dsps = null;
	
	//CommentList
	CommentList cmList = null;
	BugBodyList bbList = null;	
	DescriptionList dsList = null;
	
	//element label list
	public static HashMap<String, Integer> elementLabelList = null;
	static
	{
		elementLabelList = new HashMap<String, Integer>();
		
		elementLabelList.put("bug", BUG);
		elementLabelList.put("bugid", BUGID);
		elementLabelList.put("title", TITLE);
		elementLabelList.put("status", STATUS);
		elementLabelList.put("owner", OWNER);
		elementLabelList.put("closedOn", CLOSEDON);
		elementLabelList.put("type", TYPE);
		elementLabelList.put("priority", PRIORITY);
		elementLabelList.put("component", COMPONENT);
		elementLabelList.put("stars", STARS);
		elementLabelList.put("reportedBy", REPORTEDBY);
		elementLabelList.put("openedDate", OPENEDDATE);
		elementLabelList.put("description", DESCRIPTION);
		elementLabelList.put("comment", COMMENT);
		elementLabelList.put("author", AUTHOR);
		elementLabelList.put("when", WHEN);
		elementLabelList.put("what", WHAT);
	}
	
	//flag for readable characters
	boolean isReadable = false;
	
	public class BugXMLParser extends DefaultHandler
	{		
		//record the bugid
    	String bugid = null;
    	
    	//object for bugbody, description and comment
    	BugBody bb = null;
    	Description ds = null;
    	Comment cm = null;
    	
    	//store current element
    	String elementContent = null;
		
		public BugXMLParser()
	    {
	    	super();

	    	//instantiate bd, ds and co
	    	bb = new BugBody();
	    	ds = new Description();
	    	cm = new Comment();
	    }
	    
	    public void startDocument()
	    {

	    }
	    
	    public void endDocument()
	    {
	    	//push the remained items into db
	    	try
	    	{
	    		cmList.outsource();
	    	}
	    	catch(Exception e)
	    	{
	    		e.printStackTrace();
	    	}
	    	
	    	try
	    	{
	    		bbList.outsource();
	    	}
	    	catch(Exception e)
	    	{
	    		e.printStackTrace();
	    	}
	    	
	    	try
	    	{
	    		dsList.outsource();
	    	}
	    	catch(Exception e)
	    	{
	    		e.printStackTrace();
	    	}
	    }
	    
	    
	    public void startElement (String uri, String name, 
	    		String qName, Attributes atts)
	    {	
	    	//get current element's label
	    	Integer currentElement = elementLabelList.get(name.trim());
	    	
	    	elementContent = null;
	    	
	    	if (currentElement != null 
	    			&& currentElement >= BUG && currentElement <= COMMENT) 
	    	{
				switch (currentElement) 
				{
					case BUG: 
					{
						//cb = new ChangeBody();
						
						isReadable = false;
						
			    		//elementContent = null;
						
						break;
					}
					case COMMENT: 
					{						
						isReadable = false;
						//cm = new Comment();
						
			    		//elementContent = null;
											
						break;
					}
					default: 
					{
						if (currentElement >= BUGID && currentElement <= WHAT)
						{
							isReadable = true;
						
							//elementContent = new String();
						}
						
						break;
					}
				}
	    		
	    	}
	    	else
	    	{
				isReadable = false;
	    	}

	    }
	    
	    public void endElement (String uri, String name, String qName)
	    {  		
	    	//get current element's label
	    	Integer currentElement = elementLabelList.get(name.trim());

	    	if(currentElement != null 
	    			&& currentElement >= BUG && currentElement <= COMMENT)
	    	{
		    	switch(currentElement)
		    	{
		    		case COMMENT:
		    		{	
		    			cm.setElement(BUGID, bugid);
		    			
		    			/*
		    			if (cm.getElement(BUGID).equals("2885") 
		    					&& cm.getElement(AUTHOR).equals("jbq+legacy@google.com"))
		    			{
		    				
		    				System.err.println("-->" + cm.getElement(WHAT) + "<--");
		    				System.err.println(cm.getElement(WHAT).equals(""));
		    			}
		    			*/
		    			
		    			//add this comment element to its list
		    			try
		    			{
							cmList.add(cm);
		    			}
		    			catch(Exception e)
		    			{
		    				e.printStackTrace();
		    			}
		    			
		    			break;
		    		}
		    		case AUTHOR:
		    		{
		    			cm.setElement(AUTHOR, elementContent);
		    			
		    			break;
		    		}
		    		case WHEN:
		    		{
		    			cm.setElement(WHEN, elementContent);
		    			
		    			break;
		    		}
		    		case WHAT:
		    		{
		    			cm.setElement(WHAT, elementContent);
		    			
		    			break;
		    		}
		    		case DESCRIPTION:
		    		{
		    			ds.setElement(BUGID, bugid);
		    			ds.setElement(DESCRIPTION, elementContent);
		    			
		    			//add this description element to its list
		    			try
		    			{
							dsList.add(ds);
		    			}
		    			catch(Exception e)
		    			{
		    				e.printStackTrace();
		    			}
		    			
		    			break;
		    		}
		    		case BUG:
		    		{
		    			//add this bugBody element to its list
		    			try
		    			{
		    				/*
							if (bb.getElement(BUGID).equals("19727"))
							{
								System.err.println(bb.getElement(CLOSEDON)== null);
							}
							*/
		    				
		    				bbList.add(bb);
		    			}
		    			catch(Exception e)
		    			{
		    				e.printStackTrace();
		    			}
		    			
		    			break;
		    		}
		    		case BUGID:
		    		{
		    			//record the bugid
		    			bugid = elementContent;
		    			
		    			bb.setElement(BUGID, elementContent);
		    			
		    			break;
		    		}
		    		default:
		    		{	
		    			if (isReadable && currentElement >= BUGID
		    					&& currentElement <= OPENEDDATE)
		    			{
		    				//normal element, just add it to cb
		    				bb.setElement(elementLabelList.get(name), 
		    						normalize(elementContent));
		    			}
		    			break;
		    		}    	
		    	}
	    	}
	    	
	    	//reset this flag to make following text unreadable
			isReadable = false;
			
			elementContent= null;
	    }
	    
	    public void characters (char ch[], int start, int length)
	    {
	    	//read in characters when current element is meaningful
	    	if (isReadable == true)
	    	{
	    		//System.out.println(new String(ch, start, length));
	    		if ((elementContent != null) || (length > 0))
	    		{	    		
		    		elementContent = (elementContent == null) 
		    				? normalize(new String(ch, start, length))
		    				: elementContent + normalize(new String(ch, start, length));
	    		}

			
	    	}
	    }
	}
	
	
	public BugReader(String filename) throws Exception
	{
		//pass in the filepath
		this.filename = filename;
		
		//instantiate an XML reader
		xr = XMLReaderFactory.createXMLReader();
		
		//set event handler and error handler
		BugXMLParser handler = new BugXMLParser();
		xr.setContentHandler(handler);
		xr.setErrorHandler(handler);
		
		//set up db
		db = new DB();
		
		try
		{
			db.connect();
			
			db.dropTable("bugBody");
			db.buildTable("bugBody");
			
			db.dropTable("comment");
			db.buildTable("comment");
			
			db.dropTable("description");
			db.buildTable("description");
			
		}
		catch (Exception e)
		{
			e.printStackTrace();
		}
    	
		//set up changebody, message, target lists
    	cmList = new CommentList(db.getPreparedStatement("comment"));
    	bbList = new BugBodyList(db.getPreparedStatement("bugBody"));
    	dsList = new DescriptionList(db.getPreparedStatement("description"));
    	
	}
	
	private void parseXML()
	{
		try
		{
			xr.parse(filename);
		}
		catch(Exception e)
		{
			e.printStackTrace();
		}
	}
	
    public static String normalize(String str)
    {
    	if (str != null && !str.trim().equalsIgnoreCase("null") 
    			&& !str.trim().equals(""))
    	{
    		str = str.trim();
    		
    		return StringEscapeUtils.escapeHtml(str);
    		
    		//return str;
    	}
    	
    	return null;
    }
	
	public static Timestamp toTimestamp(String timeStream)
	{
		//System.err.println(timeStream);
		
		if (timeStream.length() == 31)
		{
			//the day
			String day = new String(timeStream.substring(5, 7));
			
			//the month
			String monthString = new String(timeStream.substring(8, 11));	
			
			//convert into numerical month
			int month = -1;
			char firstLetter = Character.toUpperCase(timeStream.charAt(8));
			switch(firstLetter)
			{
				case 'J':
				{
					if (Character.toUpperCase(timeStream.charAt(9)) == 'A')
					{
						month = 0;
					}
						else if (Character.toUpperCase(timeStream.charAt(10)) == 'N')
						{
							month = 5;
						}
						else
						{
							month = 6;
						}
					
					break;
				}
				case 'F':
				{
					month = 1;
					
					break;
				}
				case 'M':
				{
					if (Character.toUpperCase(timeStream.charAt(10)) == 'R')
					{
						month = 2;
					}
					else
					{
						month = 4;
					}
					
					break;
				}
				case 'A':
				{
					if (Character.toUpperCase(timeStream.charAt(9)) == 'P')
					{
						month = 3;
					}
					else
					{
						month = 7;
					}
					
					break;
				}
				case 'S':
				{
					month = 8;
					
					break;
				}
				case 'O':
				{
					month = 9;
					
					break;
				}
				case 'N':
				{
					month = 10;
					
					break;
				}
				case 'D':
				{
					month = 11;
					
					break;
				}
				default:
				{
					break;
				}
			}
	
			//year
			String year = new String(timeStream.substring(12, 16));
			
			//hour
			String hour = new String(timeStream.substring(17, 19));
			
			//minute
			String min = new String(timeStream.substring(20, 22));
			
			//second
			String sec = new String(timeStream.substring(23, 25));
		
			//sign
			int sign = timeStream.charAt(26) == '+' ? 1 : -1;
			
			//offset
			String hourOffset = new String(timeStream.substring(27, 29));
			
			String minOffset = new String(timeStream.substring(29, 31));	
			
			int timeOffset = sign * (Integer.parseInt(hourOffset) * 3600000 
					+ Integer.parseInt(minOffset) * 60000);
		
			
			//set up a timezone
			SimpleTimeZone tZone = new SimpleTimeZone(timeOffset, "");		
			
			Calendar dayTime = Calendar.getInstance();
			
			dayTime.setTimeZone(tZone);
			
			//set up the time
			dayTime.set(Integer.parseInt(year), month, Integer.parseInt(day),
					Integer.parseInt(hour), Integer.parseInt(min), 
					Integer.parseInt(sec));
	
			//System.out.println(dayTime.getTime().toString());
	
			return new Timestamp(dayTime.getTime().getTime());
		}
		else if (timeStream.length() == 24)
		{
	
			//year
			String year = new String(timeStream.substring(0, 4));
			
			//month
			String month = new String(timeStream.substring(5, 7));
			
			//day
			String day = new String(timeStream.substring(8, 10));
			
			//hour
			String hour = new String(timeStream.substring(11, 13));
			
			//minute
			String min = new String(timeStream.substring(14, 16));
			
			//second
			String sec = new String(timeStream.substring(17, 19));
			
			Calendar dayTime = Calendar.getInstance();
			
			dayTime.set(Integer.parseInt(year), Integer.parseInt(month), 
					Integer.parseInt(day), Integer.parseInt(hour), 
					Integer.parseInt(min), Integer.parseInt(sec));
				
			//System.err.println(timeStream);
			
			return new Timestamp(dayTime.getTime().getTime());
			}

		return null;
	}
	
	public static void main(String args[]) throws Exception
	{
		DateFormat dateFormat = new SimpleDateFormat("yyyy/MM/dd HH:mm:ss");
		Date date = new Date();
		System.out.println(dateFormat.format(date));
		
		File dir = new File("data");
		String filepath = dir.getAbsolutePath() + "/android_platform_bugs.xml";
		
		BugReader br = new BugReader(filepath);
		
		//System.out.println(filepath);
		
		br.parseXML();
		
		//db.dropAll();
		db.close();
		
		date = new Date();
		System.out.println(dateFormat.format(date));

	}
}
